/*
 * $Header: /data/cvs/gsd/src/java/com/gs/common/dao/sql/SqlParser.java,v 1.2 2007/09/17 03:12:46 clarkliu Exp $
 * $Revision: 1.2 $
 * $Date: 2007/09/17 03:12:46 $
 *
 * ====================================================================
 *
 * Copyright (c) 2006 Media Data Systems Pte Ltd All Rights Reserved.
 * This software is the confidential and proprietary information of
 * Media Data Systems Pte Ltd.You shall not disclose such Confidential
 * Information.
 *
 * ====================================================================
 *
 */
package io.renren.utils.util;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
import org.apache.log4j.Logger;

public class SqlParser {

	public static final int TYPE_UNKNOWN = Integer.MIN_VALUE;
	
	private static Logger logger = Logger.getLogger(SqlParser.class);

	/**
	 * SQLInfo VO,store sql detail information
	 */
	public static class SQLInfo {
		private String querySQL;

		private String queryFields;

		private String[] fieldArray;

		private String queryFrom;

		private String queryWhere;

		private String queryOrderBy;

		public String[] getFieldArray() {
			return fieldArray;
		}

		public void setFieldArray(String[] fieldArray) {
			this.fieldArray = fieldArray;
		}

		public String getQueryFields() {
			return queryFields;
		}

		public void setQueryFields(String queryFields) {
			this.queryFields = queryFields;
		}

		public String getQueryFrom() {
			return queryFrom;
		}

		public void setQueryFrom(String queryFrom) {
			this.queryFrom = queryFrom;
		}

		public String getQueryOrderBy() {
			return queryOrderBy;
		}

		public void setQueryOrderBy(String queryOrderBy) {
			this.queryOrderBy = queryOrderBy;
		}

		public String getQuerySQL() {
			return querySQL;
		}

		public void setQuerySQL(String querySQL) {
			this.querySQL = querySQL;
		}

		public String getQueryWhere() {
			return queryWhere;
		}

		public void setQueryWhere(String queryWhere) {
			this.queryWhere = queryWhere;
		}

		public String toString() {
			return ToStringBuilder.reflectionToString(this, ToStringStyle.MULTI_LINE_STYLE);
		}
	}
	
	/**
	 * paser the inputed sql, format as below:
	 * select ... from ... where ... order by ...
	 * 
	 * @param querySQL
	 * @return
	 */
	public static SQLInfo parserComplexQuerySQL(String querySQL) {
		SQLInfo info = new SQLInfo();
		int firstSelectPos = querySQL.indexOf("select");
		int lastFromPos = querySQL.lastIndexOf("from");
		int lastWherePos = querySQL.lastIndexOf("where");
		int lastOrderByPos = querySQL.lastIndexOf("order by");
		info.setQueryFields(querySQL.substring(firstSelectPos + 7, lastFromPos).trim());
		info.setQueryFrom(querySQL.substring(lastFromPos + 5, lastWherePos).trim());
		if (lastOrderByPos >= 0) {
			info.setQueryWhere(querySQL.substring(lastWherePos + 6, lastOrderByPos).trim());
			info.setQueryOrderBy(querySQL.substring(lastOrderByPos + 9).trim());
		} else {
			info.setQueryWhere(querySQL.substring(lastWherePos + 6).trim());
		}
		info.setQuerySQL(querySQL);
		logger.debug("orginal sql [" + querySQL + "]");
		logger.debug("pasered sql [" + info + "]");
		return info;
	}

	/**
	 * paser the inputed sql, format as below:
	 * select ... from ... where ... order by ...
	 * 
	 * @param querySQL
	 * @return
	 */
	public static SQLInfo parserQuerySQL(String querySQL) {
		SQLInfo info = new SQLInfo();
		String[] columnNames = null;
		String field = "";
		info.setQueryFields(StringUtils.substringBetween(querySQL, "select", "from"));
		info.setQueryFrom(StringUtils.substringBetween(querySQL, "from", "where"));
		if (StringUtils.indexOf(querySQL, "order by") >= 0) {
			info.setQueryWhere(StringUtils.substringBetween(querySQL, "where", "order by"));
		} else {
			info.setQueryWhere(StringUtils.substringAfter(querySQL, "where"));
		}
		info.setQueryOrderBy(StringUtils.substringAfter(querySQL, "order by"));
		String[] fields = StringUtils.split(info.getQueryFields(), ",");
		StringBuffer result = new StringBuffer("");
		List m_columnNames = new ArrayList();
		for (int i = 0; i < fields.length; i++) {
			if (i > 0) {
				result.append(",");
			}
			field = fields[i];
			field = StringUtils.trim(field);
			field = StringUtils.chomp(field);
			m_columnNames.add(field);
			result.append(field);
		}
		columnNames = new String[m_columnNames.size()];
		info.setFieldArray((String[]) m_columnNames.toArray(columnNames));
		info.setQueryFields(StringUtils.join(info.getFieldArray(), ","));
		StringBuffer sql = new StringBuffer("select " + result + " from " + info.getQueryFrom());
		if (info.getQueryWhere() != null && !"".equals(info.getQueryWhere())) {
			sql.append(" where " + info.getQueryWhere());
		}
		if (info.getQueryOrderBy() != null && !"".equals(info.getQueryOrderBy())) {
			sql.append(" order by " + info.getQueryOrderBy());
		}
		info.setQuerySQL(sql.toString());
		logger.debug("orginal sql [" + querySQL + "]");
		logger.debug("pasered sql [" + info + "]");
		return info;
	}
}